--/test
drop table if exists tmp.tmp_trade_shops;
create table tmp.tmp_trade_shops as
(
    select 
        dim.shopid, dim.userid, dim.areaid, dim.shopname, dim.shoplevel, dim.status, dim.createtime, dim.modifytime,
        dim.start_dt,
        case when ods.shopid is not null and dim.modifytime<=>ods.modifytime then dim.end_dt --当日没有变化的记录
        else '2020-07-14'                                                                --删除的、当日变化的记录的旧纪录
        end as end_dt
    from dim.dim_trade_shops dim left join (select * from ods.ods_trade_shops where dt='2020-07-14') ods
    on dim.shopid=ods.shopid
    union all
    select
        ods.shopid, ods.userid, ods.areaid, ods.shopname, ods.shoplevel, ods.status, ods.createtime, ods.modifytime,
        case  when ods.modifytime is not null 
            then substr(ods.modifytime,1,10)
            else substr(ods.createtime,1,10) 
        end as start_dt,
        '9999-12-31' as end_dt --新增的、当日变化记录的新记录
    from (select * from ods.ods_trade_shops where dt='2020-07-14') ods left join (select * from dim.dim_trade_shops where end_dt>='9999-12-31')dim
    on dim.shopid=ods.shopid
    where dim.shopid is null or (not (ods.modifytime <=> dim.modifytime ))
);
insert overwrite table dim.dim_trade_shops select * from tmp.tmp_trade_shops;

--/roll back
insert overwrite table dim.dim_trade_shops
(select 
    shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime,start_dt, end_dt
from dim.dim_trade_shops where end_dt<'2020-07-12'                      -- end_dt 小于 rollback date 记录的在rollback日期前已经失效，无需修改
union all
select shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime,start_dt, '9999-12-31' as end_dt
from dim.dim_trade_shops where start_dt<='2020-07-12' and '2020-07-12'<=end_dt);